IF EXISTS (
    SELECT * FROM SYSOBJECTS A WHERE A.NAME = 'W_CONCILIACAO_ESTOQUE_LOJAS')
     DROP VIEW W_CONCILIACAO_ESTOQUE_LOJAS

GO
CREATE VIEW W_CONCILIACAO_ESTOQUE_LOJAS 
AS
-- 05/10/2010 - Ruy Paulo - Alterado para buscar dados da tabela LOJA_ENTRADAS_PRODUTO_DIF e LOJA_ENTRADAS_DIF
-- ALESSANDRO - 07/03/2003
	SELECT 	
		TIPO = CONVERT(VARCHAR(20),'ROMANEIO    '), ISNULL(B.EMISSAO,C.EMISSAO) AS EMISSAO, B.FILIAL AS FILIAL_SAIDA, B.ROMANEIO_PRODUTO AS ROMANEIO_NF_SAIDA,C.SERIE_NF_ENTRADA AS SERIE_NF,
		C.FILIAL AS FILIAL_ENTRADA,	C.ROMANEIO_PRODUTO AS ROMANEIO_ENTRADA,C.ROMANEIO_NF_SAIDA AS ROMANEIO_NF_SAIDA_ENTRADA, C.FILIAL_ORIGEM, 
		C.SERIE_NF_ENTRADA,	A.PRODUTO,A.COR_PRODUTO,ISNULL(A.QTDE_SAIDA,0) AS QTDE_SAIDA,ISNULL(A.VALOR,0) AS VALOR_SAIDA,
		(ISNULL(A.EN1,0)) AS SAI1,(ISNULL(A.EN2,0)) AS SAI2,(ISNULL(A.EN3,0)) AS SAI3,(ISNULL(A.EN4,0)) AS SAI4,(ISNULL(A.EN5,0)) AS SAI5,(ISNULL(A.EN6,0)) AS SAI6,
		(ISNULL(A.EN7,0)) AS SAI7,(ISNULL(A.EN8,0)) AS SAI8,(ISNULL(A.EN9,0)) AS SAI9,(ISNULL(A.EN10,0)) AS SAI10,(ISNULL(A.EN11,0)) AS SAI11,(ISNULL(A.EN12,0)) AS SAI12,
		(ISNULL(A.EN13,0)) AS SAI13,(ISNULL(A.EN14,0)) AS SAI14,(ISNULL(A.EN15,0)) AS SAI15,(ISNULL(A.EN16,0)) AS SAI16,(ISNULL(A.EN17,0)) AS SAI17,(ISNULL(A.EN18,0)) AS SAI18,
		(ISNULL(A.EN19,0)) AS SAI19,(ISNULL(A.EN20,0)) AS SAI20,(ISNULL(A.EN21,0)) AS SAI21,(ISNULL(A.EN22,0)) AS SAI22,(ISNULL(A.EN23,0)) AS SAI23,(ISNULL(A.EN24,0)) AS SAI24,
		(ISNULL(A.EN25,0)) AS SAI25,(ISNULL(A.EN26,0)) AS SAI26,(ISNULL(A.EN27,0)) AS SAI27,(ISNULL(A.EN28,0)) AS SAI28,(ISNULL(A.EN29,0)) AS SAI29,(ISNULL(A.EN30,0)) AS SAI30,
		(ISNULL(A.EN31,0)) AS SAI31,(ISNULL(A.EN32,0)) AS SAI32,(ISNULL(A.EN33,0)) AS SAI33,(ISNULL(A.EN34,0)) AS SAI34,(ISNULL(A.EN35,0)) AS SAI35,(ISNULL(A.EN36,0)) AS SAI36,
		(ISNULL(A.EN37,0)) AS SAI37,(ISNULL(A.EN38,0)) AS SAI38,(ISNULL(A.EN39,0)) AS SAI39,(ISNULL(A.EN40,0)) AS SAI40,(ISNULL(A.EN41,0)) AS SAI41,(ISNULL(A.EN42,0)) AS SAI42,
		(ISNULL(A.EN43,0)) AS SAI43,(ISNULL(A.EN44,0)) AS SAI44,(ISNULL(A.EN45,0)) AS SAI45,(ISNULL(A.EN46,0)) AS SAI46,(ISNULL(A.EN47,0)) AS SAI47,(ISNULL(A.EN48,0)) AS SAI48,
		ISNULL(C.QTDE_ENTRADA,0) AS QTDE_ENTRADA, ISNULL(C.VALOR,0) AS VALOR_ENTRADA,
		(ISNULL(C.EN1,0)) AS ENT1,(ISNULL(C.EN2,0)) AS ENT2,(ISNULL(C.EN3,0)) AS ENT3,(ISNULL(C.EN4,0)) AS ENT4,(ISNULL(C.EN5,0)) AS ENT5,(ISNULL(C.EN6,0)) AS ENT6,
		(ISNULL(C.EN7,0)) AS ENT7,(ISNULL(C.EN8,0)) AS ENT8,(ISNULL(C.EN9,0)) AS ENT9,(ISNULL(C.EN10,0)) AS ENT10,(ISNULL(C.EN11,0)) AS ENT11,(ISNULL(C.EN12,0)) AS ENT12,
		(ISNULL(C.EN13,0)) AS ENT13,(ISNULL(C.EN14,0)) AS ENT14,(ISNULL(C.EN15,0)) AS ENT15,(ISNULL(C.EN16,0)) AS ENT16,(ISNULL(C.EN17,0)) AS ENT17,(ISNULL(C.EN18,0)) AS ENT18,
		(ISNULL(C.EN19,0)) AS ENT19,(ISNULL(C.EN20,0)) AS ENT20,(ISNULL(C.EN21,0)) AS ENT21,(ISNULL(C.EN22,0)) AS ENT22,(ISNULL(C.EN23,0)) AS ENT23,(ISNULL(C.EN24,0)) AS ENT24,
		(ISNULL(C.EN25,0)) AS ENT25,(ISNULL(C.EN26,0)) AS ENT26,(ISNULL(C.EN27,0)) AS ENT27,(ISNULL(C.EN28,0)) AS ENT28,(ISNULL(C.EN29,0)) AS ENT29,(ISNULL(C.EN30,0)) AS ENT30,
		(ISNULL(C.EN31,0)) AS ENT31,(ISNULL(C.EN32,0)) AS ENT32,(ISNULL(C.EN33,0)) AS ENT33,(ISNULL(C.EN34,0)) AS ENT34,(ISNULL(C.EN35,0)) AS ENT35,(ISNULL(C.EN36,0)) AS ENT36,
		(ISNULL(C.EN37,0)) AS ENT37,(ISNULL(C.EN38,0)) AS ENT38,(ISNULL(C.EN39,0)) AS ENT39,(ISNULL(C.EN40,0)) AS ENT40,(ISNULL(C.EN41,0)) AS ENT41,(ISNULL(C.EN42,0)) AS ENT42,
		(ISNULL(C.EN43,0)) AS ENT43,(ISNULL(C.EN44,0)) AS ENT44,(ISNULL(C.EN45,0)) AS ENT45,(ISNULL(C.EN46,0)) AS ENT46,(ISNULL(C.EN47,0)) AS ENT47,(ISNULL(C.EN48,0)) AS ENT48
	FROM 
		LOJA_SAIDAS_PRODUTO A
		JOIN LOJA_SAIDAS B ON B.FILIAL = A.FILIAL AND B.ROMANEIO_PRODUTO = A.ROMANEIO_PRODUTO
		FULL JOIN ( 
					SELECT 
						C.FILIAL_ORIGEM, C.ROMANEIO_NF_SAIDA, C.SERIE_NF_ENTRADA, C.EMISSAO, D.* 
					FROM 
						LOJA_ENTRADAS_DIF C 
				 		JOIN LOJA_ENTRADAS_PRODUTO_dif D ON D.FILIAL = C.FILIAL AND D.ROMANEIO_PRODUTO = C.ROMANEIO_PRODUTO 
				 	WHERE 
				 		C.Entrada_Por = 1 /*AND  ISNULL(C.SERIE_NF_ENTRADA,'') = ''*/
				   ) AS  C ON (C.ROMANEIO_NF_SAIDA = A.ROMANEIO_PRODUTO AND C.FILIAL_ORIGEM = A.FILIAL AND C.PRODUTO = A.PRODUTO AND C.COR_PRODUTO = A.COR_PRODUTO)
	WHERE 
		A.QTDE_SAIDA > 0
		
	UNION ALL 
	
	SELECT	
		TIPO = CONVERT(VARCHAR(20),'NOTA FISCAL'), ISNULL(B.EMISSAO,C.EMISSAO) AS EMISSAO, B.FILIAL AS FILIAL_SAIDA, B.NF_SAIDA AS ROMANEIO_NF_SAIDA, B.SERIE_NF,
		C.FILIAL AS FILIAL_ENTRADA,	C.ROMANEIO_PRODUTO AS ROMANEIO_ENTRADA,C.ROMANEIO_NF_SAIDA AS ROMANEIO_NF_SAIDA_ENTRADA, C.FILIAL_ORIGEM, C.SERIE_NF_ENTRADA,
		A.PRODUTO,A.COR_PRODUTO,ISNULL(A.QTDE_SAIDA,0) AS QTDE_SAIDA,ISNULL(A.VALOR_SAIDA,0) AS VALOR_SAIDA,
		(ISNULL(A.SAI1,0)) AS SAI1,(ISNULL(A.SAI2,0)) AS SAI2,(ISNULL(A.SAI3,0)) AS SAI3,(ISNULL(A.SAI4,0)) AS SAI4,(ISNULL(A.SAI5,0)) AS SAI5,(ISNULL(A.SAI6,0)) AS SAI6,
		(ISNULL(A.SAI7,0)) AS SAI7,(ISNULL(A.SAI8,0)) AS SAI8,(ISNULL(A.SAI9,0)) AS SAI9,(ISNULL(A.SAI10,0)) AS SAI10,(ISNULL(A.SAI11,0)) AS SAI11,(ISNULL(A.SAI12,0)) AS SAI12,
		(ISNULL(A.SAI13,0)) AS SAI13,(ISNULL(A.SAI14,0)) AS SAI14,(ISNULL(A.SAI15,0)) AS SAI15,(ISNULL(A.SAI16,0)) AS SAI16,(ISNULL(A.SAI17,0)) AS SAI17,(ISNULL(A.SAI18,0)) AS SAI18,
		(ISNULL(A.SAI19,0)) AS SAI19,(ISNULL(A.SAI20,0)) AS SAI20,(ISNULL(A.SAI21,0)) AS SAI21,(ISNULL(A.SAI22,0)) AS SAI22,(ISNULL(A.SAI23,0)) AS SAI23,(ISNULL(A.SAI24,0)) AS SAI24,
		(ISNULL(A.SAI25,0)) AS SAI25,(ISNULL(A.SAI26,0)) AS SAI26,(ISNULL(A.SAI27,0)) AS SAI27,(ISNULL(A.SAI28,0)) AS SAI28,(ISNULL(A.SAI29,0)) AS SAI29,(ISNULL(A.SAI30,0)) AS SAI30,
		(ISNULL(A.SAI31,0)) AS SAI31,(ISNULL(A.SAI32,0)) AS SAI32,(ISNULL(A.SAI33,0)) AS SAI33,(ISNULL(A.SAI34,0)) AS SAI34,(ISNULL(A.SAI35,0)) AS SAI35,(ISNULL(A.SAI36,0)) AS SAI36,
		(ISNULL(A.SAI37,0)) AS SAI37,(ISNULL(A.SAI38,0)) AS SAI38,(ISNULL(A.SAI39,0)) AS SAI39,(ISNULL(A.SAI40,0)) AS SAI40,(ISNULL(A.SAI41,0)) AS SAI41,(ISNULL(A.SAI42,0)) AS SAI42,
		(ISNULL(A.SAI43,0)) AS SAI43,(ISNULL(A.SAI44,0)) AS SAI44,(ISNULL(A.SAI45,0)) AS SAI45,(ISNULL(A.SAI46,0)) AS SAI46,(ISNULL(A.SAI47,0)) AS SAI47,(ISNULL(A.SAI48,0)) AS SAI48,
		ISNULL(C.QTDE_ENTRADA,0) AS QTDE_ENTRADA, ISNULL(C.VALOR,0) AS VALOR_ENTRADA,
		(ISNULL(C.EN1,0)) AS ENT1,(ISNULL(C.EN2,0)) AS ENT2,(ISNULL(C.EN3,0)) AS ENT3,(ISNULL(C.EN4,0)) AS ENT4,(ISNULL(C.EN5,0)) AS ENT5,(ISNULL(C.EN6,0)) AS ENT6,
		(ISNULL(C.EN7,0)) AS ENT7,(ISNULL(C.EN8,0)) AS ENT8,(ISNULL(C.EN9,0)) AS ENT9,(ISNULL(C.EN10,0)) AS ENT10,(ISNULL(C.EN11,0)) AS ENT11,(ISNULL(C.EN12,0)) AS ENT12,
		(ISNULL(C.EN13,0)) AS ENT13,(ISNULL(C.EN14,0)) AS ENT14,(ISNULL(C.EN15,0)) AS ENT15,(ISNULL(C.EN16,0)) AS ENT16,(ISNULL(C.EN17,0)) AS ENT17,(ISNULL(C.EN18,0)) AS ENT18,
		(ISNULL(C.EN19,0)) AS ENT19,(ISNULL(C.EN20,0)) AS ENT20,(ISNULL(C.EN21,0)) AS ENT21,(ISNULL(C.EN22,0)) AS ENT22,(ISNULL(C.EN23,0)) AS ENT23,(ISNULL(C.EN24,0)) AS ENT24,
		(ISNULL(C.EN25,0)) AS ENT25,(ISNULL(C.EN26,0)) AS ENT26,(ISNULL(C.EN27,0)) AS ENT27,(ISNULL(C.EN28,0)) AS ENT28,(ISNULL(C.EN29,0)) AS ENT29,(ISNULL(C.EN30,0)) AS ENT30,
		(ISNULL(C.EN31,0)) AS ENT31,(ISNULL(C.EN32,0)) AS ENT32,(ISNULL(C.EN33,0)) AS ENT33,(ISNULL(C.EN34,0)) AS ENT34,(ISNULL(C.EN35,0)) AS ENT35,(ISNULL(C.EN36,0)) AS ENT36,
		(ISNULL(C.EN37,0)) AS ENT37,(ISNULL(C.EN38,0)) AS ENT38,(ISNULL(C.EN39,0)) AS ENT39,(ISNULL(C.EN40,0)) AS ENT40,(ISNULL(C.EN41,0)) AS ENT41,(ISNULL(C.EN42,0)) AS ENT42,
		(ISNULL(C.EN43,0)) AS ENT43,(ISNULL(C.EN44,0)) AS ENT44,(ISNULL(C.EN45,0)) AS ENT45,(ISNULL(C.EN46,0)) AS ENT46,(ISNULL(C.EN47,0)) AS ENT47,(ISNULL(C.EN48,0)) AS ENT48
	FROM 
		(	
			SELECT  
				FILIAL,NF_SAIDA,SERIE_NF,PRODUTO,COR_PRODUTO,SUM(QTDE) AS QTDE_SAIDA,SUM(VALOR) AS VALOR_SAIDA,
				SUM(F1) AS SAI1,SUM(F2) AS SAI2,SUM(F3) AS SAI3,SUM(F4) AS SAI4,SUM(F5) AS SAI5,SUM(F6) AS SAI6,
				SUM(F7) AS SAI7,SUM(F8) AS SAI8,SUM(F9) AS SAI9,SUM(F10) AS SAI10,SUM(F11) AS SAI11,SUM(F12) AS SAI12,
				SUM(F13) AS SAI13,SUM(F14) AS SAI14,SUM(F15) AS SAI15,SUM(F16) AS SAI16,SUM(F17) AS SAI17,SUM(F18) AS SAI18,
				SUM(F19) AS SAI19,SUM(F20) AS SAI20,SUM(F21) AS SAI21,SUM(F22) AS SAI22,SUM(F23) AS SAI23,SUM(F24) AS SAI24,
				SUM(F25) AS SAI25,SUM(F26) AS SAI26,SUM(F27) AS SAI27,SUM(F28) AS SAI28,SUM(F29) AS SAI29,SUM(F30) AS SAI30,
				SUM(F31) AS SAI31,SUM(F32) AS SAI32,SUM(F33) AS SAI33,SUM(F34) AS SAI34,SUM(F35) AS SAI35,SUM(F36) AS SAI36,
				SUM(F37) AS SAI37,SUM(F38) AS SAI38,SUM(F39) AS SAI39,SUM(F40) AS SAI40,SUM(F41) AS SAI41,SUM(F42) AS SAI42,
				SUM(F43) AS SAI43,SUM(F44) AS SAI44,SUM(F45) AS SAI45,SUM(F46) AS SAI46,SUM(F47) AS SAI47,SUM(F48) AS SAI48
			FROM 
				FATURAMENTO_PROD  WHERE QTDE > 0
			GROUP BY 
				FILIAL,NF_SAIDA,SERIE_NF,PRODUTO,COR_PRODUTO 
		) 	A 
		JOIN FATURAMENTO B ON B.FILIAL = A.FILIAL AND B.NF_SAIDA = A.NF_SAIDA AND B.SERIE_NF = A.SERIE_NF
		FULL JOIN ( 
					SELECT 
						C.FILIAL_ORIGEM,  C.ROMANEIO_NF_SAIDA, C.SERIE_NF_ENTRADA, C.EMISSAO, D.* 
					FROM 
						LOJA_ENTRADAS_DIF C 
						INNER JOIN LOJA_ENTRADAS_PRODUTO_DIF D ON D.FILIAL = C.FILIAL AND D.ROMANEIO_PRODUTO = C.ROMANEIO_PRODUTO  
					WHERE 
						C.ENTRADA_POR = 1 AND  ISNULL(C.SERIE_NF_ENTRADA,'') <> '' 
				  ) AS  C ON (C.FILIAL_ORIGEM = B.FILIAL AND C.ROMANEIO_NF_SAIDA = B.NF_SAIDA AND C.SERIE_NF_ENTRADA = B.SERIE_NF AND C.PRODUTO = A.PRODUTO AND C.COR_PRODUTO = A.COR_PRODUTO)
